const db = require("../../db");

/**
 * POST 单位的回调函数
 * @param unitName  单位名称
 * @param unitcode  单位代码
 * @param unitType  类型
 * @param remark  备注
 */
exports.add = (req, res) => {
  // 接收表单的数据
  const unitInfo = req.body;
  // 定义 SQL 语句
  let sql = `
  INSERT INTO account_in_bound 
  (unitName, unitId, dateTime, createdTime, sortName, sortId, number, totalAmount, totalAmountChinese, manager, warehouse,warehouseId, accountName, handledName, role, userName, userId, remark)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`;
  let values = [
    unitInfo.unitName,
    unitInfo.unitId,
    unitInfo.dateTime,
    Date.now(), // 例如 createdTime 使用 Date.now() 来获取当前时间戳
    unitInfo.sortName,
    unitInfo.sortId,
    unitInfo.number,
    unitInfo.totalAmount,
    unitInfo.totalAmountChinese,
    unitInfo.manager,
    unitInfo.warehouse,
    unitInfo.warehouseId,
    unitInfo.accountName,
    unitInfo.handledName,
    unitInfo.role,
    unitInfo.userName,
    unitInfo.userId,
    unitInfo.remark,
  ];
  // 调用 db.query() 执行 sql 语句
  db.query(sql, values, (error, result) => {
    if (error) return res.cc(error);
    // 判断影响行数是否为 1
    if (result.affectedRows !== 1) return res.cc("新增入库失败！");
    const insertId = result.insertId;
    if (!unitInfo.subTable || !Array.isArray(unitInfo.subTable)) {
      return res.cc("未提供有效的从表数据！");
    }
    const subTableValues = unitInfo.subTable.map((item) => ({
      pid: insertId,
      inBoundName: item.inBoundName,
      inBoundType: item.inBoundType,
      inBoundUnit: item.inBoundUnit,
      inBoundUnitName: item.inBoundUnitName,
      inBoundNum: item.inBoundNum,
      inBoundPrice: item.inBoundPrice,
      inBoundMoney: item.inBoundMoney,
      remark: unitInfo.remark,
      createdTime: Date.now(),
      warehouseId: unitInfo.warehouseId,
      warehouse: unitInfo.warehouse,
      storeNum: item.inBoundNum,
      role: unitInfo.role,
      userName: unitInfo.userName,
      userId: unitInfo.userId,
    }));
    // 定义 SQL 语句插入从表
    let sql2 =
      "insert into account_in_bound_detail (pid, inBoundName, inBoundType, inBoundUnit, inBoundUnitName, inBoundNum, inBoundPrice, inBoundMoney,remark, createdTime, warehouseId, warehouse, storeNum, role, userName, userId) values ?";
    db.query(
      sql2,
      [subTableValues.map((subItem) => Object.values(subItem))],
      (error, result) => {
        if (error) return res.cc(error);
        if (result.affectedRows !== subTableValues.length) {
          return res.cc("新增入库单失败！");
        }
        return res.cc("新增入库单成功", true);
      }
    );
  });
};

exports.update = (req, res) => {
  const unitInfo = req.body;

  db.beginTransaction((error) => {
    if (error) {
      console.error("Transaction Begin Error:", error);
      return res.cc("Transaction begin failed.");
    }

    const updateMainTableSQL = `
      UPDATE account_in_bound 
      SET
        unitName = ?,
        unitId = ?,
        dateTime = ?,
        sortName = ?,
        sortId = ?,
        number = ?,
        totalAmount = ?,
        totalAmountChinese = ?,
        manager = ?,
        warehouse = ?,
        warehouseId = ?,
        accountName = ?,
        handledName = ?,
        userName = ?,
        userId = ?,
        remark = ?
      WHERE id = ?
    `;

    const mainTableValues = [
      unitInfo.unitName,
      unitInfo.unitId,
      unitInfo.dateTime,
      unitInfo.sortName,
      unitInfo.sortId,
      unitInfo.number,
      unitInfo.totalAmount,
      unitInfo.totalAmountChinese,
      unitInfo.manager,
      unitInfo.warehouse,
      unitInfo.warehouseId,
      unitInfo.accountName,
      unitInfo.handledName,
      unitInfo.userName,
      unitInfo.userId,
      unitInfo.remark,
      unitInfo.id,
    ];

    db.query(updateMainTableSQL, mainTableValues, (error, mainTableResult) => {
      if (error) {
        console.error("Main Table Update Error:", error);
        return db.rollback(() => res.cc("Main table update failed."));
      }

      if (mainTableResult.affectedRows !== 1) {
        console.error("Main Table Update Warning:", mainTableResult);
        return db.rollback(() => res.cc("Main table not updated as expected."));
      }

      const deleteDetailsTableSQL = `
        DELETE FROM account_in_bound_detail
        WHERE pid = ?
      `;

      db.query(deleteDetailsTableSQL, [unitInfo.id], (error, deleteResult) => {
        if (error) {
          console.error("Delete Details Table Error:", error);
          return db.rollback(() =>
            res.cc("Failed to delete old details entries.")
          );
        }

        const insertDetailsTableSQL = `
          INSERT INTO account_in_bound_detail (
            inBoundName, inBoundType, inBoundUnit, inBoundUnitName,
            inBoundNum, inBoundPrice, inBoundMoney, remark, createdTime,
            warehouseId, warehouse, storeNum, role, userName, userId, pid
          ) VALUES ?
        `;

        const subTableValues = unitInfo.subTable.map((item) => [
          item.inBoundName,
          item.inBoundType,
          item.inBoundUnit,
          item.inBoundUnitName,
          item.inBoundNum,
          item.inBoundPrice,
          item.inBoundMoney,
          unitInfo.remark,
          item.createdTime,
          unitInfo.warehouseId,
          unitInfo.warehouse,
          item.storeNum,
          unitInfo.role,
          unitInfo.userName,
          unitInfo.userId,
          unitInfo.id,
        ]);

        db.query(
          insertDetailsTableSQL,
          [subTableValues],
          (error, detailsTableResult) => {
            if (error) {
              console.error("Insert Details Table Error:", error);
              return db.rollback(() => res.cc("Details table insert failed."));
            }

            db.commit((error) => {
              if (error) {
                console.error("Transaction Commit Error:", error);
                return res.cc("Transaction commit failed.");
              }

              return res.cc("更新入库单成功!", true);
            });
          }
        );
      });
    });
  });
};

exports.del = (req, res) => {
  // 接收表单的数据
  const boundInfo = req.body;
  let sql = "select * from account_in_bound where id=?";
  db.query(sql, [boundInfo.id], (error, result) => {
    if (error) {
      return res.cc(error);
    }
    if (result.length === 0) {
      return res.cc("未找到该入库单记录!");
    }
    // 定义删除子表记录的 SQL 语句
    const deleteDetailSql = "DELETE FROM account_in_bound_detail WHERE pid=?";
    // 定义删除主表记录的 SQL 语句
    const deleteMainSql = "DELETE FROM account_in_bound WHERE id=?";
    db.query(deleteDetailSql, [boundInfo.id], (error, result) => {
      if (error) return res.cc(error);

      db.query(deleteMainSql, [boundInfo.id], (error, result) => {
        if (error) return res.cc(error);

        if (result.affectedRows !== 1) return res.cc("删除入库单失败！");

        return res.cc("删除入库单成功", true);
      });
    });
  });
};

exports.allList = (req, res) => {
  const page = req.body.page ? parseInt(req.body.page) : 1;
  const limit = req.body.limit ? parseInt(req.body.limit) : 10;
  const {
    unitName,
    sortName = "",
    totalAmount = "",
    createdTime = [],
    remark,
  } = req.body;
  const userInfo = req.headers["userinfo"];
  const decodedUserInfo = decodeURIComponent(userInfo);
  const userInfoObject = JSON.parse(decodedUserInfo);
  const { role, userId } = userInfoObject;
  const sqlCount =
    role === "1"
      ? "SELECT COUNT(*) AS total FROM account_in_bound"
      : "SELECT COUNT(*) AS total FROM account_in_bound WHERE userId = ?";
  const countParams = role === "1" ? [] : [userId];

  db.query(sqlCount, countParams, (error, countResults) => {
    if (error) {
      return res.cc(error);
    }

    const total = countResults[0].total;
    const offset = (page - 1) * limit;
    let sql;
    let params;
    if (role === "1") {
      // If the role is 1, query all records
      sql = `
        SELECT * 
        FROM account_in_bound 
        WHERE 
          (unitName LIKE ? OR ? IS NULL) 
          AND (sortName LIKE ? OR ? IS NULL) 
          AND (totalAmount LIKE ? OR ? IS NULL)
          AND (remark LIKE ? OR ? IS NULL)
          AND (createdTime >= ? AND createdTime <= ? OR ? IS NULL)
        ORDER BY createdTime DESC 
        LIMIT ? OFFSET ?
      `;
      params = [
        `%${unitName}%`,
        unitName,
        `%${sortName}%`,
        sortName,
        `%${totalAmount}%`,
        totalAmount,
        `%${remark}%`,
        remark,
        createdTime[0],
        createdTime[1],
        createdTime[0],
        limit,
        offset,
      ];
    } else {
      sql = `
        SELECT * 
        FROM account_in_bound 
        WHERE 
          userId = ?
          AND (unitName LIKE ? OR ? IS NULL) 
          AND (sortName LIKE ? OR ? IS NULL) 
          AND (totalAmount LIKE ? OR ? IS NULL)
          AND (remark LIKE ? OR ? IS NULL)
          AND (createdTime >= ? AND createdTime <= ? OR ? IS NULL)
        ORDER BY createdTime DESC 
        LIMIT ? OFFSET ?
      `;
      params = [
        userId,
        `%${unitName}%`,
        unitName,
        `%${sortName}%`,
        sortName,
        `%${totalAmount}%`,
        totalAmount,
        `%${remark}%`,
        remark,
        createdTime[0],
        createdTime[1],
        createdTime[0],
        limit,
        offset,
      ];
    }

    db.query(sql, params, (error, results) => {
      if (error) {
        return res.cc(error);
      }
      return res.cc("获取单据成功", true, results, {
        total,
        page,
        limit,
      });
    });
  });
};

exports.detail = (req, res) => {
  const boundInfo = req.body;
  // 查询主表数据
  let sql = "SELECT * FROM account_in_bound WHERE id=?";
  db.query(sql, [boundInfo.id], (error, mainResult) => {
    if (error) {
      return res.cc(error);
    }
    if (mainResult.length === 0) {
      return res.cc("未找到该入库单记录!");
    }

    // 查询子表数据
    let subSql = "SELECT * FROM account_in_bound_detail WHERE pid=?";
    db.query(subSql, [boundInfo.id], (error, subResult) => {
      if (error) {
        return res.cc(error);
      }

      const mainData = mainResult[0];
      mainData.subTable = subResult; // 将子表数据整合到主表数据中

      return res.cc("获取入库单详情成功", true, mainData);
    });
  });
};

exports.allGoods = (req, res) => {
  const boundInfo = req.body;
  let sql = `SELECT * FROM account_in_bound_detail where warehouseId =?`;
  db.query(sql, [boundInfo.warehouseId], (error, results) => {
    if (error) {
      return res.cc(error);
    }
    // 用于存储结果的对象
    const resultMap = {};
    // 遍历数组进行求和操作
    results.forEach((item) => {
      const key = item.inBoundName + "_" + item.inBoundType;
      if (resultMap[key]) {
        resultMap[key].inBoundNum += item.inBoundNum;
        resultMap[key].inBoundMoney += item.inBoundMoney;
      } else {
        resultMap[key] = { ...item };
      }
    });
    // 将 resultMap 转回数组形式
    const resultArray = Object.values(resultMap);
    return res.cc("获取列表成功", true, resultArray);
  });
};

exports.allGoodsList = (req, res) => {
  const boundInfo = req.body;
  let sql = `SELECT * FROM account_in_bound_detail where warehouseId =?`;
  db.query(sql, [boundInfo.warehouseId], (error, results) => {
    if (error) {
      return res.cc(error);
    }
    return res.cc("获取列表成功", true, results);
  });
};

exports.getStoreNumList = (req, res) => {
  const boundInfo = req.body;
  const warehouseId = boundInfo.warehouseId;
  const id = boundInfo.id;
  // 查询入库表的总数
  const sqlInBound = `
  SELECT
  inBoundName,
     inBoundType,
  COALESCE(SUM(inBoundNum), 0) - COALESCE(SUM(outBoundNum), 0) AS storeNum
  FROM
  (
     SELECT
  inBoundName,
     inBoundType,
     inBoundNum,
     0 AS outBoundNum
  FROM
  account_in_bound_detail
  WHERE
  warehouseId = ?
     AND id = ?
        UNION ALL
  SELECT
  outBoundName AS inBoundName,
     outBoundType,
     0 AS inBoundNum,
     outBoundNum
  FROM
  account_out_bound_detail
  WHERE
  warehouseId = ?
     AND outBoundId = ?
) AS combinedData
  GROUP BY
  inBoundName,
     inBoundType;
    `;

  db.query(
    sqlInBound,
    [warehouseId, id, warehouseId, id],
    (errorOut, results) => {
      if (errorOut) {
        return res.cc(errorOut);
      }
      // 计算差值
      // const diffArray = {
      //   inBoundName: resultsIn[0].inBoundName,
      //   inBoundType: resultsIn[0].inBoundType,
      //   storeNum: resultsIn[0].totalInBoundNum - (resultsOut.length > 0 ? resultsOut[0].totalOutBoundNum : 0),
      // }

      return res.cc("获取物品列表成功", true, results);
    }
  );
};

exports.warehouseStoreList = (req, res) => {
  const page = req.body.page ? parseInt(req.body.page) : 1;
  const limit = req.body.limit ? parseInt(req.body.limit) : 10;
  const inBoundName = req.body.inBoundName || "";
  const userInfo = req.headers["userinfo"];
  const decodedUserInfo = decodeURIComponent(userInfo);
  const userInfoObject = JSON.parse(decodedUserInfo);

  if (userInfoObject.role === "1") {
    // 如果是管理员，直接返回全部数据
    const sqlCount =
      "SELECT COUNT(*) AS total FROM account_in_bound_detail where storeNum > 0";
    db.query(sqlCount, (error, countResults) => {
      if (error) {
        return res.cc(error);
      }
      const total = countResults[0].total;
      const offset = (page - 1) * limit;
      let sql;
      let params;

      if (inBoundName) {
        sql = `
          SELECT * 
          FROM account_in_bound_detail 
          WHERE inBoundName LIKE ? 
          AND storeNum > 0
          ORDER BY createdTime DESC 
          LIMIT ? OFFSET ?
        `;
        params = [`%${inBoundName}%`, limit, offset];
      } else {
        sql = `
          SELECT * 
          FROM account_in_bound_detail 
          where storeNum > 0 
          ORDER BY createdTime DESC 
          LIMIT ? OFFSET ?
        `;
        params = [limit, offset];
      }

      db.query(sql, params, (error, results) => {
        if (error) {
          return res.cc(error);
        }
        return res.cc("获取列表成功", true, results, {
          total,
          page,
          limit,
        });
      });
    });
  } else {
    // 如果不是管理员，先查询对应的 warehouseId
    const warehouseSql =
      "SELECT warehouseId FROM account_warehouse_man WHERE userId = ?";
    db.query(
      warehouseSql,
      [userInfoObject.userId],
      (error, warehouseResults) => {
        if (error) {
          return res.cc(error);
        }
        console.log("this1----warehouseResults", warehouseResults);
        if (warehouseResults.length === 0) {
          // 如果没有对应的 warehouseId，返回空数组
          return res.cc("获取列表成功", true, [], {
            total: 0,
            page,
            limit,
          });
        }

        // 如果有对应的 warehouseId，构建查询条件
        const warehouseIds = warehouseResults.map(
          (result) => result.warehouseId
        );
        const sqlCount = `
        SELECT COUNT(*) AS total 
        FROM account_in_bound_detail 
        WHERE warehouseId IN (?${", ?".repeat(warehouseIds.length - 1)})
        AND inBoundName LIKE ?
        AND storeNum > 0
      `;
        const countParams = [...warehouseIds, `%${inBoundName}%`];

        const offset = (page - 1) * limit;
        let sql = `
        SELECT * 
        FROM account_in_bound_detail 
        WHERE warehouseId IN (?${", ?".repeat(warehouseIds.length - 1)})
        AND inBoundName LIKE ?
        AND storeNum > 0
        ORDER BY createdTime DESC 
        LIMIT ? OFFSET ?
      `;
        const params = [...warehouseIds, `%${inBoundName}%`, limit, offset];
        console.log("this1----", params);
        db.query(sqlCount, countParams, (error, countResults) => {
          if (error) {
            return res.cc(error);
          }
          const total = countResults[0].total;

          db.query(sql, params, (error, results) => {
            if (error) {
              return res.cc(error);
            }
            return res.cc("获取列表成功", true, results, {
              total,
              page,
              limit,
            });
          });
        });
      }
    );
  }
};

// 获取当前年月，格式为 YYYY-MM
function getCurrentYearMonth(dateTime) {
  // const today = new Date();
  // const year = today.getFullYear();
  // const month = (today.getMonth() + 1).toString().padStart(2, "0");
  const time = new Date(dateTime);
  const year = time.getFullYear();
  const month = time.getMonth() + 1;
  return `${year}-${month}`;
}

// 将数字填充到指定位数，不足的位数用0填充
function padNumber(number, width) {
  return number.toString().padStart(width, "0");
}

exports.getMaxNumber = (req, res) => {
  const warehouseId = req.body.warehouseId;
  const dateTime = req.body.dateTime;
  // 查询最新的出库单编号
  const sql = `
    SELECT MAX(number) AS latestInBoundNo
    FROM account_in_bound
    WHERE warehouseId = ? AND YEAR(FROM_UNIXTIME(dateTime/1000)) = YEAR(FROM_UNIXTIME(?/1000)) AND MONTH(FROM_UNIXTIME(dateTime/1000)) = MONTH(FROM_UNIXTIME(?/1000));
  `;
  db.query(sql, [warehouseId, dateTime, dateTime], (error, results) => {
    if (error) {
      return res.cc(error);
    }
    // 获取最新的出库单编号
    console.log(1, results);
    const latestInBoundNo = results[0].latestInBoundNo;
    // 生成新的编号
    let newInBoundNo;
    if (!latestInBoundNo) {
      // 如果没有最新编号，生成初始编号
      newInBoundNo = `${getCurrentYearMonth(dateTime)}-0001`;
    } else {
      // 如果有最新编号，将其加1
      const latestNumber = parseInt(latestInBoundNo.split("-")[2]);
      const newNumber = latestNumber + 1;
      newInBoundNo = `${getCurrentYearMonth(dateTime)}-${padNumber(
        newNumber,
        4
      )}`;
    }
    // 在这里使用 newInBoundNo 进行其他操作，或返回给客户端
    return res.cc("获取最新入库单编号成功", true, { newInBoundNo });
  });
};
